package belf.migrate.plugin.oracle;

import belf.migrate.api.exception.APIErrorCode;
import belf.migrate.api.exception.CatalogException;
import belf.migrate.api.table.schema.UserDefSeq;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class OracleDDLBuilder {

    public Map<String, String> buildSequenceDDL(Connection connection, String owner, List<String> sequenceNames) throws CatalogException {
        Map<String, String> seqDDLs = new HashMap<>();
        String sql = """
                SELECT
                    SEQUENCE_NAME,
                    MIN_VALUE,
                    MAX_VALUE,
                    INCREMENT_BY,
                    CYCLE_FLAG,
                    CACHE_SIZE,
                    ORDER_FLAG
                FROM
                    USER_SEQUENCES
                """;
        if (null != sequenceNames && !sequenceNames.isEmpty()) {
            List<String> quotedSeqNames = new ArrayList<>();
            for (String seqName : sequenceNames) {
                quotedSeqNames.add("'" + seqName + "'");
            }
            sql += String.format(" WHERE SEQUENCE_NAME IN (%s)", String.join(",", quotedSeqNames));
        }

        try (Statement stmt = connection.createStatement()){
            try (ResultSet rs = stmt.executeQuery(sql)){
                while (rs.next()) {
                    String seqName = rs.getString("SEQUENCE_NAME");
                    long minValue = rs.getLong("MIN_VALUE");
                    long maxValue = Long.MAX_VALUE;
                    try {
                        maxValue = rs.getLong("MAX_VALUE");
                    } catch (SQLException e) {
                    }
                    long incrementBy = rs.getLong("INCREMENT_BY");
                    String cycleFlag = rs.getString("CYCLE_FLAG");
                    long cacheSize = rs.getLong("CACHE_SIZE");
                    String orderFlag = rs.getString("ORDER_FLAG");
                    StringBuilder sb = new StringBuilder();
                    String template = String.format("CREATE SEQUENCE %s.%s INCREMENT BY %d MINVALUE %d MAXVALUE %d",
                            owner,
                            seqName,
                            incrementBy,
                            minValue,
                            maxValue);
                    sb.append(template);
                    if ("N".equalsIgnoreCase(cycleFlag)) {
                        sb.append(" NOCYCLE");
                    }
                    if (cacheSize == 0) {
                        sb.append(" NOCACHE");
                    }
                    if ("N".equalsIgnoreCase(orderFlag)) {
                        sb.append(" NOORDER");
                    }
                    String ddl = sb.toString();
                    seqDDLs.put(seqName, ddl);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new CatalogException(APIErrorCode.SQL_EXECUTE_ERROR, "查询Oracle所有序列，请检查SQL语句：sql=" + sql, e);
        }

        return seqDDLs;
    }
}
